// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Kailua.net.windward.utils;

namespace net.windward.utils.ado.SqlServer
{
	/// <summary>
	/// A SqlServer database.
	/// </summary>
	public class WrSqlServerDatabase : IWrDatabase, IComparable
	{
		private readonly DbProviderFactory provider;
		private readonly string server;
		private readonly string database;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] storedProcedures;

		private static readonly Hashtable types;
		private static readonly int debugDelaySeconds = -1;

		static WrSqlServerDatabase()
		{
			types = new Hashtable();
			types.Add("numeric", DbType.Decimal);

			NameValueCollection appSettings = ConfigurationManager.AppSettings;
			string str = appSettings["select-delay"];
			if (string.IsNullOrEmpty(str))
				return;

			int num;
			if (int.TryParse(str, out num))
				if ((0 < num) && (num < 120))
					debugDelaySeconds = num;
		}

		/// <summary>
		/// Create a SqlServer database object.
		/// </summary>
		/// <param name="provider">The provider for this server.</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrSqlServerDatabase(DbProviderFactory provider, string server, string database)
		{
			this.provider = provider;
			this.server = server;
			this.database = database;
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return "dbo";
			}
		}

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return storedProcedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(GetCommandLine(credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.START, database);
			DebugPause();

			using (DbConnection conn = provider.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();
				int verNum = GetSqlVerNum(conn);

				if (status != null)
					status(WrDatabase.TYPE.TABLES, null);

				Dictionary<string, string> descTables = new Dictionary<string, string>(); 
				if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
					try
					{
						descTables = verNum <= 8 ? TableDescVer8(conn, "BASE TABLE") : TableDescVer9(conn, "sys.tables");
					}
					catch (Exception)
					{
						// nada
						Trap.trap();
					}
				Dictionary<string, string> descColumns = new Dictionary<string, string>();
				if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
					try
					{
						descColumns = verNum <= 8 ? ColDescVer8(conn) : ColDescVer9(conn);
					}
					catch (Exception)
					{
						// nada
						Trap.trap();
					}

				// get the joins
				Dictionary<string, string> pkMaps = new Dictionary<string, string>();
				if ((owner & WrVendor.ELEM_OWNER.KEY_JOINS) != 0)
					try
					{
						pkMaps = verNum <= 8 ? FkPkMappingsVer8(conn) : FkPkMappingsVer9(conn);
					}
					catch (Exception)
					{
						// nada
						Trap.trap();
					}

				// read in the table/columns
				using (DbCommand cmd = provider.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = "SELECT t.table_name, t.table_schema, c.COLUMN_NAME, c.DATA_TYPE FROM INFORMATION_SCHEMA.TABLES as t, INFORMATION_SCHEMA.COLUMNS as c " +
									"WHERE (t.table_name = c.table_name) and (t.table_schema = c.table_schema) and (t.table_type = 'BASE TABLE') and (t.TABLE_NAME != 'dtproperties') ORDER BY t.table_schema,  t.table_name";

					string tableOn = "", schemaOn = "";
					List<WrTable> listTables = new List<WrTable>();
					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						List<WrColumn> listColumns = new List<WrColumn>();
						while (myReader.Read())
						{
							string tableName = myReader.GetString(0).Trim();
							string schemaName = myReader.GetString(1).Trim();
							if ((tableName != tableOn) || (schemaName != schemaOn))
							{
								if (tableOn.Length > 0)
								{
									string str2 = FullName(schemaOn, tableOn);
									listTables.Add(new WrTable(schemaOn, tableOn, descTables.ContainsKey(str2) ? descTables[str2] : null, false, listColumns.ToArray()));
								}

								schemaOn = schemaName;
								tableOn = tableName;
								listColumns.Clear();

								if (status != null)
									status(WrDatabase.TYPE.TABLE, tableName);
							}

							string columnName = myReader.GetString(2).Trim();
							DbType typ = ConvertFromSqlDbType(myReader.GetString(3));
							string str = FullName(schemaOn, tableOn, columnName);
							listColumns.Add(new WrColumn(columnName, descColumns.ContainsKey(str) ? descColumns[str] : null, typ, pkMaps.ContainsKey(str) ? pkMaps[str] : null));
						}
						if (tableOn.Length > 0)
						{
							string str2 = FullName(schemaOn, tableOn);
							listTables.Add(new WrTable(schemaOn, tableOn, descTables.ContainsKey(str2) ? descTables[str2] : null, false, listColumns.ToArray()));
						}
					}
					tables = listTables.ToArray();
				}

				// now the views
				if (status != null)
						status(WrDatabase.TYPE.VIEWS, null);

				descTables.Clear();
				if ((owner & WrVendor.ELEM_OWNER.DESCRIPTIONS) != 0)
					try
					{
						descTables = verNum <= 8 ? TableDescVer8(conn, "VIEW") : TableDescVer9(conn, "sys.views");
					}
					catch (Exception)
					{
						// nada
						Trap.trap();
					}

				using (DbCommand cmd = provider.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					cmd.CommandText = "SELECT t.table_name, t.table_schema, c.COLUMN_NAME, c.DATA_TYPE FROM INFORMATION_SCHEMA.TABLES as t, INFORMATION_SCHEMA.COLUMNS as c " +
						"WHERE (t.TABLE_NAME = c.table_name) and (t.table_schema = c.table_schema) and (t.table_type = 'VIEW') and (t.TABLE_NAME != 'sysconstraints') and (t.TABLE_NAME != 'syssegments') ORDER BY t.table_schema, t.table_name";

					string tableOn = "", schemaOn = "";
					List<WrView> listViews = new List<WrView>();
					List<WrColumn> listColumns = new List<WrColumn>();
					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string tableName = myReader.GetString(0).Trim();
							string schemaName = myReader.GetString(1).Trim();
							if ((tableName != tableOn) || (schemaName != schemaOn))
							{
								if (tableOn.Length > 0)
								{
									string str2 = FullName(schemaOn, tableOn);
									listViews.Add(new WrView(schemaOn, tableOn, descTables.ContainsKey(str2) ? descTables[str2] : null, false, listColumns.ToArray()));
								}

								schemaOn = schemaName;
								tableOn = tableName;
								listColumns.Clear();

								if (status != null)
									status(WrDatabase.TYPE.TABLE, tableName);
							}

							string columnName = myReader.GetString(2).Trim();
							DbType typ = ConvertFromSqlDbType(myReader.GetString(3));
							string str = FullName(schemaOn, tableOn, columnName);
							listColumns.Add(new WrColumn(columnName, descColumns.ContainsKey(str) ? descColumns[str] : null, typ, pkMaps.ContainsKey(str) ? pkMaps[str] : null));
						}
						if (tableOn.Length > 0)
						{
							string str2 = FullName(schemaOn, tableOn);
							listViews.Add(new WrView(schemaOn, tableOn, descTables.ContainsKey(str2) ? descTables[str2] : null, false, listColumns.ToArray()));
						}
					}
					views = listViews.ToArray();
				}
			}

			storedProcedures = GetStoredProcedures(connStr, owner, status);
		}

		private Dictionary<string, string> FkPkMappingsVer8(DbConnection conn)
		{

			return FkPkMappings(conn, "SELECT USER_NAME() as parent_schema_name, OBJECT_NAME(sfk.fkeyid) as parent_table_name, scf.name AS parent_column_name, " +
						"USER_NAME(sos.uid) AS referenced_table_schema, OBJECT_NAME(sfk.rkeyid) AS referenced_table_name, scr.name AS referenced_column_name " +
						"FROM sysforeignkeys sfk INNER JOIN syscolumns scf ON sfk.fkeyid = scf.id AND sfk.fkey = scf.colid " +
						"INNER JOIN syscolumns scr ON sfk.rkeyid = scr.id AND sfk.rkey = scr.colid " +
						"INNER JOIN sysobjects sos ON sfk.rkeyid = sos.id");
		}

		private Dictionary<string, string> FkPkMappingsVer9(DbConnection conn)
		{
			return FkPkMappings(conn, "SELECT OBJECT_SCHEMA_NAME(fkc.parent_object_id) as parent_table_schema, OBJECT_NAME(fkc.parent_object_id) as parent_table_name, " +
								  "scp.name AS parent_column_name, OBJECT_SCHEMA_NAME(fkc.referenced_object_id) AS referenced_table_schema, " +
								  "OBJECT_NAME(fkc.referenced_object_id) AS referenced_table_name, scr.name AS referenced_column_name FROM sys.foreign_key_columns AS fkc " +
								  "INNER JOIN sys.columns scp ON fkc.parent_object_id = scp.object_id AND fkc.parent_column_id = scp.column_id INNER JOIN sys.columns scr ON " +
								  "fkc.referenced_object_id = scr.object_id AND fkc.referenced_column_id =scr.column_id");
		}

		private Dictionary<string, string> FkPkMappings(DbConnection conn, string select)
		{
			using (DbCommand cmd = provider.CreateCommand())
			{
				cmd.Connection = conn;
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = select;

				Dictionary<string, string> nameDesc = new Dictionary<string, string>();
				using (DbDataReader myReader = cmd.ExecuteReader())
				{
					// parent_schema, parent_table, parent_column, referenced_schema, referenced_table, referenced_column
					while (myReader.Read())
					{
						string parent = FullName(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2));
						Trap.trap(nameDesc.ContainsKey(parent));	// bugbug - does this happen?
						if (nameDesc.ContainsKey(parent)) 
							continue;
						string schema = myReader.GetString(3);
						string fk = string.IsNullOrEmpty(schema) ? "" : schema.Trim() + '.';
						fk += myReader.GetString(4).Trim() + '.' + myReader.GetString(5).Trim();
						nameDesc.Add(parent, fk);
					}
				}
				return nameDesc;
			}
		}

		private Dictionary<string, string> TableDescVer8(DbConnection conn, string sysTable)
		{
			return TableDesc(conn, "Select t.TABLE_SCHEMA, t.TABLE_NAME, ep.value FROM INFORMATION_SCHEMA.TABLES as t, dbo.sysproperties as ep " +
				                  "WHERE ep.id = object_id(t.TABLE_NAME) AND t.TABLE_TYPE = '" + sysTable + "' AND ep.type=3 AND ep.name = 'MS_Description' and ep.value is not null");
		}

		private Dictionary<string, string> TableDescVer9(DbConnection conn, string sysTable)
		{
			return TableDesc(conn, "SELECT OBJECT_SCHEMA_NAME(t.object_id) as SchemaName, t.name AS TableName, ex.value AS Description FROM " +
								  sysTable + " AS t, sys.extended_properties AS ex WHERE ex.major_id = t.object_id AND ex.minor_id=0 AND " +
								  "ex.name = 'MS_Description' and ex.value is not null");
		}

		private Dictionary<string, string> TableDesc(DbConnection conn, string select)
		{
			using (DbCommand cmd = provider.CreateCommand())
			{
				cmd.Connection = conn;
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = select;

				Dictionary<string, string> nameDesc = new Dictionary<string, string>();
				using (DbDataReader myReader = cmd.ExecuteReader())
				{
					// SchemaName, TableName, Description
					while (myReader.Read())
					{
						string name = FullName(myReader.GetString(0).ToLower(), myReader.GetString(1).ToLower());
						string desc = myReader.GetString(2).Trim();
						Trap.trap(string.IsNullOrEmpty(desc));	//bugbug - does this happen?
						if (!string.IsNullOrEmpty(desc))
							nameDesc.Add(name, desc);
					}
				}
				return nameDesc;
			}
		}

		private Dictionary<string, string> ColDescVer8(DbConnection conn)
		{
			return ColDesc(conn, "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, sysproperties.[value] FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN " +
								  "sysproperties ON sysproperties.id = OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA + '.' + " +
								  "INFORMATION_SCHEMA.COLUMNS.TABLE_NAME) AND sysproperties.smallid = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION " +
								  "AND sysproperties.name = 'MS_Description' WHERE (OBJECTPROPERTY(OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  " +
								  "+ '.' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME), 'IsMsShipped') = 0) and (sysproperties.[value] is not null)");
		}

		private Dictionary<string, string> ColDescVer9(DbConnection conn)
		{
			return ColDesc(conn, "SELECT OBJECT_SCHEMA_NAME(c.object_id) as SchemaName, OBJECT_NAME(c.object_id) as TableName, c.name AS ColumnName, ex.value AS Description " +
								  "FROM sys.columns AS c INNER JOIN sys.extended_properties AS ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND " +
								  "ex.name = 'MS_Description' AND  ex.class_desc = 'OBJECT_OR_COLUMN' WHERE (OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0) AND " +
								  "ex.value is not null");
		}

		private Dictionary<string, string> ColDesc(DbConnection conn, string select)
		{
			using (DbCommand cmd = provider.CreateCommand())
			{
				cmd.Connection = conn;
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = select;

				Dictionary<string, string> nameDesc = new Dictionary<string, string>();
				using (DbDataReader myReader = cmd.ExecuteReader())
				{
					// SchemaName, TableName, ColumnNames, Description
					while (myReader.Read())
					{
						string name = FullName(myReader.GetString(0), myReader.GetString(1), myReader.GetString(2));
						string desc = myReader.GetString(3).Trim();
						Trap.trap(string.IsNullOrEmpty(desc));	// bugbug - does this happen?
						Trap.trap(nameDesc.ContainsKey(name));	// bugbug - does this happen?
						if ((!string.IsNullOrEmpty(desc)) && (!nameDesc.ContainsKey(name)))
							nameDesc.Add(name, desc);
					}
				}
				return nameDesc;
			}
		}

		private DbType ConvertFromSqlDbType(string name)
		{
			// some don't convert
			string key = name.ToLower();
			if (types.Contains(key))
				return (DbType)types[key];

			try
			{
				SqlDbType typ = (SqlDbType)Enum.Parse(typeof(SqlDbType), name, true);
				System.Data.SqlClient.SqlParameter param = provider.CreateParameter() as System.Data.SqlClient.SqlParameter;
				if (param != null)
				{
					param.SqlDbType = typ;
					return param.DbType;
				}
				return DbType.Object;
			}
			catch (Exception)
			{
				return DbType.Object;
			}
		}

		/// <summary>
		/// Return all stored procedures in this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Set if to get all user, all system, or all (both) stored procedures.</param>
		/// <returns>all stored procedures in this database.</returns>
		/// <param name="status">Pass the status of loading the metadata.</param>
		private WrStoredProcedure[] GetStoredProcedures(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			DebugPause();
			if (status != null)
				status(WrDatabase.TYPE.PROCEDURES, null);

			List<TableInfo> listProcs = new List<TableInfo>();
			using (DbConnection conn = provider.CreateConnection())
			{

				conn.ConnectionString = connStr;
				conn.Open();

				if (GetSqlVerNum(conn) <= 8)
					using (DataTable schemaTable = conn.GetSchema("procedures"))
					{
						int ordSchema = schemaTable.Columns.IndexOf("SPECIFIC_SCHEMA");
						int ordName = schemaTable.Columns.IndexOf("SPECIFIC_NAME");

						foreach (DataRow rowOn in schemaTable.Rows)
						{
							string schema = TrimString(rowOn, ordSchema);
							if (string.IsNullOrEmpty(schema) || (schema == DefaultSchema))
								schema = null;
							string name = TrimString(rowOn, ordName);
							bool isSys = name.StartsWith("dt_") || name.StartsWith("sp_");
							if ((isSys && ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)) ||
								((!isSys) && ((owner & (WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.USER_OWNED)) != 0)))
							{
								listProcs.Add(new TableInfo(schema, name, ""));

								if (status != null)
									status(WrDatabase.TYPE.PROCEDURE, name);
							}
						}
					}
				else
				using (DbCommand cmd = provider.CreateCommand())
				{
					cmd.Connection = conn;
					cmd.CommandType = CommandType.Text;
					owner &= WrVendor.ELEM_OWNER.SYSTEM | WrVendor.ELEM_OWNER.USER;
					switch (owner)
					{
						case WrVendor.ELEM_OWNER.USER_OWNED:
						case WrVendor.ELEM_OWNER.USER:
							cmd.CommandText = "SELECT s.name AS schema_name, p.name AS procedure_name FROM " +
											  "sys.schemas AS s INNER JOIN sys.procedures AS p ON s.schema_id = p.schema_id WHERE " +
											  "(p.is_ms_shipped = 0) ORDER BY schema_name, procedure_name";
							break;
						case WrVendor.ELEM_OWNER.SYSTEM:
							cmd.CommandText = "SELECT s.name AS schema_name, p.name AS procedure_name FROM " +
											  "sys.schemas AS s INNER JOIN sys.procedures AS p ON s.schema_id = p.schema_id WHERE " +
											  "(p.is_ms_shipped = 1) ORDER BY schema_name, procedure_name";
							break;
						case WrVendor.ELEM_OWNER.SYSTEM | WrVendor.ELEM_OWNER.USER:
							cmd.CommandText = "SELECT s.name AS schema_name, p.name AS procedure_name FROM " +
											  "sys.schemas AS s INNER JOIN sys.procedures AS p ON s.schema_id = p.schema_id " +
											  "ORDER BY schema_name, procedure_name";
							break;
						default:
							Trap.trap();
							return new WrStoredProcedure[0];
					}

					using (DbDataReader myReader = cmd.ExecuteReader())
					{
						while (myReader.Read())
						{
							string schema = myReader.GetString(0).Trim();
							if (string.IsNullOrEmpty(schema) || (schema == DefaultSchema))
								schema = null;
							string name = myReader.GetString(1).Trim();
							bool isSys = name.StartsWith("dt_") || name.StartsWith("sp_");

							if ((isSys && ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)) ||
								((!isSys) && ((owner & (WrVendor.ELEM_OWNER.USER | WrVendor.ELEM_OWNER.USER_OWNED)) != 0)))
							{
								listProcs.Add(new TableInfo(schema, name, ""));

								if (status != null)
									status(WrDatabase.TYPE.PROCEDURE, name);
							}
						}
					}
				}
			}
			List<WrStoredProcedure> rtn = new List<WrStoredProcedure>();
			foreach (TableInfo ti in listProcs)
			{
				WrProcParam[] procParams = GetParameters(ti.name, connStr, owner);
				rtn.Add(new WrStoredProcedure(ti.schema, ti.name, ti.description, false, procParams));
			}
			rtn.Sort();
			return rtn.ToArray();
		}

		private class TableInfo
		{
			public readonly string schema;
			public readonly string name;
			public readonly string description;

			public TableInfo(string schema, string name, string description)
			{
				this.schema = schema;
				this.name = name;
				this.description = description;
			}
		}

		/// <summary>
		/// Return all parameters in a stored procedure.
		/// </summary>
		/// <param name="procedure">The name of the stored procedure.</param>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what columns are returned and if metadata is also retrieved.</param>
		/// <returns>all columns in a table or view.</returns>
		public WrProcParam[] GetParameters(string procedure, string connStr, WrVendor.ELEM_OWNER owner)
		{

			ArrayList rtn = new ArrayList();
			using (DbConnection conn = provider.CreateConnection())
			{
				conn.ConnectionString = connStr;
				conn.Open();
				// bugbug - set schema
				string[] restrictions = new string[] { null, null, procedure, null };
				DataTable schemaTable = conn.GetSchema("ProcedureParameters", restrictions);

				int ordMode = schemaTable.Columns.IndexOf("PARAMETER_MODE");
				int ordParamName = schemaTable.Columns.IndexOf("PARAMETER_NAME");
				int ordType = schemaTable.Columns.IndexOf("DATA_TYPE");
				int ordLength = schemaTable.Columns.IndexOf("CHARACTER_MAXIMUM_LENGTH");

				foreach (DataRow rowOn in schemaTable.Rows)
				{
					ParameterDirection dir;
					switch (TrimString(rowOn, ordMode))
					{
						case "IN":
							dir = ParameterDirection.Input;
							break;
						case "INOUT":
							dir = ParameterDirection.InputOutput;
							break;
						case "OUT":
							dir = ParameterDirection.Output;
							break;
						default:
							Trap.trap();
							dir = ParameterDirection.InputOutput;
							break;
					}
					int len;
					if (rowOn[ordLength] is int)
						len = (int)rowOn[ordLength];
					else
						int.TryParse(TrimString(rowOn, ordLength), out len);
					rtn.Add(new WrProcParam(TrimString(rowOn, ordParamName), ConvertFromSqlDbType(TrimString(rowOn, ordType)), len,
											false, dir));
				}
			}
			return (WrProcParam[])rtn.ToArray(typeof(WrProcParam));
		}

		// get the sql version number
		private int GetSqlVerNum(DbConnection conn)
		{
			string version = "";
			using (DbCommand cmd = provider.CreateCommand())
			{
				cmd.Connection = conn;
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = "SELECT SERVERPROPERTY('productversion')";

				using (DbDataReader myReader = cmd.ExecuteReader())
				{
					if (myReader.Read())
						version = myReader.GetString(0);
				}
			}
			int verNum = 9;
			int pos = version.IndexOf('.');
			if (pos != -1)
				verNum = Convert.ToInt32(version.Substring(0, pos));
			return verNum;
		}

		private static string FullName(string schemaOn, string tableOn)
		{
			return string.IsNullOrEmpty(schemaOn) ? tableOn.ToLower() : (schemaOn + '.' + tableOn).ToLower();
		}

		private static string FullName(string schemaOn, string tableOn, string columnOn)
		{
			return FullName(schemaOn, tableOn) + '.' + columnOn.ToLower();
		}

		private static string TrimString(DataRow row, int index)
		{

			string name = row[index] as string ?? "";
			int pos = name.IndexOf((char)0);
			if (pos != -1)
				name = name.Substring(0, pos);
			pos = name.IndexOf((char)0);
			if (pos > 0)
				name = name.Substring(0, pos);
			return name.Trim();
		}

		private string GetCommandLine(WrCredentials credentials)
		{
			return WrSqlServerVendor.ConnectionString(provider, server, database, credentials, true);
		}

		// can add a pause on each select in debug mode
		private static void DebugPause()
		{

#if DEBUG
			if (debugDelaySeconds > 0)
				System.Threading.Thread.Sleep(debugDelaySeconds * 1000);
#endif
		}

		#region Implementation of IComparable

		/// <summary>
		/// Compares the current instance with another object of the same type.
		/// </summary>
		/// <returns>
		/// A 32-bit signed integer that indicates the relative order of the objects being compared. The return value has these meanings: Value Meaning Less than zero This instance is less than <paramref name="obj" />. Zero This instance is equal to <paramref name="obj" />. Greater than zero This instance is greater than <paramref name="obj" />. 
		/// </returns>
		/// <param name="obj">An object to compare with this instance. </param>
		/// <exception cref="T:System.ArgumentException"><paramref name="obj" /> is not the same type as this instance. </exception><filterpriority>2</filterpriority>
		public int CompareTo(object obj)
		{
			WrSqlServerDatabase db = obj as WrSqlServerDatabase;
			if (db == null)
				return 1;
			return database.CompareTo(db.database);
		}

		#endregion
	}
}
